###############################################################################
# SuperLinks                                                                  #
# Copyright (c) 2000-2007 Greg Nolle (http://greg.nolle.co.uk)                #
###############################################################################
# This program is free software; you can redistribute it and/or modify it     #
# under the terms of the GNU General Public License as published by the Free  #
# Software Foundation; either version 2 of the License, or (at your option)   #
# any later version.                                                          #
#                                                                             #
# This program is distributed in the hope that it will be useful, but WITHOUT #
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or       #
# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for   #
# more details.                                                               ##                                                                             #
# You should have received a copy of the GNU General Public License along     #
# with this program; if not, write to the Free Software Foundation, Inc.,     #
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.                 #
###############################################################################
# ControlPanel/ImportExport.pm.pl -> ImportExport module                      #
###############################################################################
# DON'T EDIT BELOW UNLESS YOU KNOW WHAT YOU'RE DOING!                         #
###############################################################################
package CP::ImportExport;

BEGIN { require "System.pm.pl"; import System qw($SYSTEM); }
BEGIN { require "General.pm.pl"; import General qw($GENERAL); }
require "Error.pm.pl";
require "Standard.pm.pl";
require "Authenticate.pm.pl";
require "Logs.pm.pl";

use File::Copy;
use strict;

require "ControlPanel/Output/ImportExport.pm.pl";

sub new {
  my ($class) = @_;
  my $self    = {};
  return bless($self, $class);
}

sub DESTROY { }

###############################################################################
# show subroutine
sub show {
  my $self = shift;
  my %in = (DB => undef, @_);

  #----------------------------------------------------------------------#
  # Authenticating...                                                    #
  
  &Authenticate::CP(DB => $in{'DB'}, ACTION => "IMPORTEXPORT");

  #----------------------------------------------------------------------#
  # Preparing data...                                                    #

  opendir(DIR, "$SYSTEM->{'DB_PATH'}");
  my @Tables;
  foreach my $file (grep(/\.cfg$/, readdir(DIR))) {
    $file =~ m/^(.*)\.cfg$/;
    push(@Tables, $1);
  }
  closedir(DIR);

  my %INPUT;
  
  $INPUT{'TABLES'} = \@Tables;

  #----------------------------------------------------------------------#
  # Printing page...                                                     #

  my $Skin = Skin::CP::ImportExport->new();

  &Standard::PrintHTMLHeader();
  print $Skin->show(input => \%INPUT);
  
  return 1;
}

###############################################################################
# do subroutine
sub do {
  my $self = shift;
  my %in = (DB => undef, @_);

  #----------------------------------------------------------------------#
  # Authenticating...                                                    #
  
  &Authenticate::CP(DB => $in{'DB'}, ACTION => "IMPORTEXPORT");

  #----------------------------------------------------------------------#
  # Preparing data...                                                    #

  if ($SL::QUERY{'Type'} eq "Import") {
    $self->myImport(DB => $in{'DB'}) and return 1;
  } elsif ($SL::QUERY{'Type'} eq "Export") {
    $self->myExport(DB => $in{'DB'}) and return 1;
  } else {
    $self->show(DB => $in{'DB'});
    return 1;
  }
}

###############################################################################
# myExport subroutine
sub myExport {
  my $self = shift;
  my %in = (DB => undef, @_);

  #----------------------------------------------------------------------#
  # Checking fields...                                                   #

  if ($SL::QUERY{'FORM_TABLE'} eq "" || $SL::QUERY{'FORM_FORMAT'} eq "") {
    $self->show(DB => $in{'DB'});
    return 1;
  }

  #----------------------------------------------------------------------#
  # Preparing data...                                                    #

  my $Table      = $SL::QUERY{'FORM_TABLE'};

  require "$SYSTEM->{'DB_PATH'}/$Table.cfg";

  my $DBTable    = $DB::Table{ $Table };
  my $Columns    = $DB::Columns{ $Table };
  my $PrimaryKey = $DB::PrimaryKey{ $Table };

  my ($Records, $RecordsIndex) = $in{'DB'}->Query(
    TABLE   => $Table,
    SORT    => $PrimaryKey,
    BY      => "A-Z"
  );

  if ($SL::QUERY{'FORM_FORMAT'} eq "Excel") {
    eval("use Spreadsheet::WriteExcel");
    &Error::CGIError("Error requiring Spreadsheet::WriteExcel module. $@", "") if ($@);

    print $SL::CGI->header(-type => "application/vnd.ms-excel", -attachment => $Table.".xls");
    
    my $Book   = Spreadsheet::WriteExcel->new("-");
    my $Sheet  = $Book->add_worksheet();
    my $Format = $Book->add_format();
    
    $Sheet->repeat_rows(0);
    
    $Format->set_bold(1);
    $Format->set_color("white");
    $Format->set_bg_color("gray");
    $Format->set_border(1);

    for (my $h = 0; $h <= $#$Columns; $h++) {
      $Sheet->write(0, $h, $Columns->[$h], $Format);
    }

    my $Row = 1;
    
    foreach my $record (@{ $Records }) {
      for (my $h = 0; $h <= $#$Columns; $h++) {
        $Sheet->write($Row, $h, $record->{ $Columns->[$h] });
      }
      $Row++;
    }
    
    $Book->close();
  } else {
    print $SL::CGI->header(-type => "application/octet-stream", -attachment => $Table.".csv");

    my @Columns;
    foreach my $column (@{ $Columns }) {
      my $tmp = $column;
         $tmp =~ s/\"/\"\"/g;
      push(@Columns, "\"$tmp\"");
    }
    
    print join(",", @Columns)."\n";
    
    foreach my $record (@{ $Records }) {
      my @tmp;
      foreach my $column (@{ $Columns }) {
        if ($DBTable->{$column}->[1] =~ /number/) {
          push(@tmp, $record->{$column});
        } else {
          $record->{$column} =~ s/\"/\"\"/g;
          push(@tmp, "\"$record->{$column}\"");
        }
      }
      print join(",", @tmp)."\n";
    }
  }
  
  return 1;    
}

###############################################################################
# myImport subroutine
sub myImport {
  my $self = shift;
  my %in = (DB => undef, @_);

  #----------------------------------------------------------------------#
  # Checking fields...                                                   #

  if ($SL::QUERY{'FORM_TABLE'} eq "" || $SL::QUERY{'FORM_FORMAT'} eq "") {
    $self->show(DB => $in{'DB'});
    return 1;
  }

  unless ($SL::QUERY{'do'}) {
    my $File = $SL::CGI->param('FORM_DATA');
    my $TempFile = $SL::CGI->tmpFileName($File);
    unless ($File && $TempFile && -e $TempFile) {
      $self->show(DB => $in{'DB'});
      return 1;
    }
    if (-e "$SYSTEM->{'TEMP_PATH'}/import.dat") {
      unlink("$SYSTEM->{'TEMP_PATH'}/import.dat") || &Error::Error("CP", MESSAGE => "Couldn't remove old temporary file. $!");
    }
    copy($TempFile, "$SYSTEM->{'TEMP_PATH'}/import.dat") || &Error::Error("CP", MESSAGE => "Couldn't copy temporary file. $!");
  }

  #----------------------------------------------------------------------#
  # Preparing data...                                                    #

  my $Skin = Skin::CP::ImportExport->new();

  &Standard::PrintHTMLHeader();  

  unless ($SL::QUERY{'do'}) {
    print $Skin->do();
    return 1;
  }

  my $Log = Logs->new(PRINT => 1);

  my $Exit = eval {
    sub {
      $Log->Footer();
      exit;
    }
  };

  $Log->Header();

  $Log->Line("", "uplevel");
  $Log->Line("Beginning import process.\n\n", "element");

  $Log->Line("Checking data file... ", "element");
  unless (-e "$SYSTEM->{'TEMP_PATH'}/import.dat") {
    $Log->Line("Error: Can't find temp file. $!", "error");
    &$Exit;
  }
  $Log->Line("Done.", "success");
  
  $Log->Line("Getting existing records... ", "element");
  my $Table      = $SL::QUERY{'FORM_TABLE'};

  require "$SYSTEM->{'DB_PATH'}/$Table.cfg";

  my $DBTable    = $DB::Table{ $Table };
  my $Columns    = $DB::Columns{ $Table };
  my $PrimaryKey = $DB::PrimaryKey{ $Table };

  my ($Records, $RecordsIndex) = $in{'DB'}->Query(
    TABLE   => $Table,
    SORT    => $PrimaryKey,
    BY      => "A-Z"
  );

  $Log->Line("Done.", "success");
  
  if ($SL::QUERY{'FORM_FORMAT'} eq "Excel") {
    $Log->Line("Opening Excel file... ", "element");
    eval("use Spreadsheet::ParseExcel");
    ($Log->Line("Error: Can't require Spreadsheet::ParseExcel module. $@", "error") and &$Exit) if ($@);
    my $Excel = Spreadsheet::ParseExcel->new();
    my $Book  = $Excel->Parse("$SYSTEM->{'TEMP_PATH'}/import.dat")
      || ($Log->Line("Error: Can't parse file", "error") and &$Exit);
    my $Sheet = $Book->{'Worksheet'}->[0];
    $Log->Line("Done.", "success");
    
    $Log->Line("Getting column names... ", "element");
    my %Columns;
    for (my $h = $Sheet->{'MinCol'}; $h <= $Sheet->{'MaxCol'}; $h++) {
      $Columns{ $Sheet->Cell(0, $h)->Value() } = $h;
    }
    $Log->Line("Done.", "success");
    
    $Log->Line("Importing data...", "element");
    $Log->Line("", "uplevel");
    
    if ($Sheet->{'MaxRow'} >= 1) {
      for (my $h = 1; $h <= $Sheet->{'MaxRow'}; $h++) {
        $Log->Line("Importing ", "element");
        my %row;
        foreach my $column (keys %Columns) {
          my $cell = $Sheet->Cell($h, $Columns{$column});
          $row{ $column } = ($cell ? $cell->Value() : "");
        }
        
        unless ($row{$PrimaryKey}) {
          $Log->Line("Error: Can't find primary key", "error");
          &$Exit;
        }
        $Log->Line("\"".$row{$PrimaryKey}."\" (".(defined($RecordsIndex->{ $row{$PrimaryKey} }) ? "updating" : "inserting").")... ", "");
        
        $row{'CREATE_DATE'} ||= &Standard::ConvertEpochToDate($row{'CREATE_SECOND'}) if ($row{'CREATE_SECOND'});
        $row{'CREATE_TIME'} ||= &Standard::ConvertEpochToTime($row{'CREATE_SECOND'}) if ($row{'CREATE_SECOND'});
        $row{'UPDATE_DATE'} ||= &Standard::ConvertEpochToDate($row{'UPDATE_SECOND'}) if ($row{'UPDATE_SECOND'});
        $row{'UPDATE_TIME'} ||= &Standard::ConvertEpochToTime($row{'UPDATE_SECOND'}) if ($row{'UPDATE_SECOND'});
        
        if (defined($RecordsIndex->{ $row{$PrimaryKey} })) {
          (
            $in{'DB'}->Update(
              TABLE   => $Table,
              VALUES  => \%row,
              KEY     => $row{$PrimaryKey}
            )
          ) || ($Log->Line("Error: Can't update record. $in{'DB'}->{'ERROR'}", "error") and &$Exit);
        } else {
          (
            $in{'DB'}->Insert(
              TABLE   => $Table,
              VALUES  => \%row,
              SKIP    => (defined($row{$PrimaryKey}) ? 1 : 0)
            )
          ) || ($Log->Line("Error: Can't insert record. $in{'DB'}->{'ERROR'}", "error") and &$Exit);
        }
        
        $Log->Line("Done.", "success");
      }
    }
    
    $Log->Line("", "downlevel");
  } else {
    $Log->Line("Opening CSV file... ", "element");
    eval("use Text::xSV");
    ($Log->Line("Error: Can't require Text::xSV module. $@", "error") and &$Exit) if ($@);
    my $CSV = Text::xSV->new(filename => "$SYSTEM->{'TEMP_PATH'}/import.dat");
       $CSV->bind_header();
    $Log->Line("Done.", "success");
    
    $Log->Line("Importing data...", "element");
    $Log->Line("", "uplevel");

    while ($CSV->get_row()) {
      $Log->Line("Importing ", "element");
      
      my %row;
      foreach my $column (keys %{$CSV->{'field_pos'}}) {
        ($row{ $column }) = $CSV->extract($column);
      }
      
      unless ($row{$PrimaryKey}) {
        $Log->Line("Error: Can't find primary key", "error");
        &$Exit;
      }
      $Log->Line("\"".$row{$PrimaryKey}."\" (".(defined($RecordsIndex->{ $row{$PrimaryKey} }) ? "updating" : "inserting").")... ", "");
      
      $row{'CREATE_DATE'} ||= &Standard::ConvertEpochToDate($row{'CREATE_SECOND'}) if ($row{'CREATE_SECOND'});
      $row{'CREATE_TIME'} ||= &Standard::ConvertEpochToTime($row{'CREATE_SECOND'}) if ($row{'CREATE_SECOND'});
      $row{'UPDATE_DATE'} ||= &Standard::ConvertEpochToDate($row{'UPDATE_SECOND'}) if ($row{'UPDATE_SECOND'});
      $row{'UPDATE_TIME'} ||= &Standard::ConvertEpochToTime($row{'UPDATE_SECOND'}) if ($row{'UPDATE_SECOND'});
        
      if (defined($RecordsIndex->{ $row{$PrimaryKey} })) {
        (
          $in{'DB'}->Update(
            TABLE   => $Table,
            VALUES  => \%row,
            KEY     => $row{$PrimaryKey}
          )
        ) || ($Log->Line("Error: Can't update record. $in{'DB'}->{'ERROR'}", "error") and &$Exit);
      } else {
        (
          $in{'DB'}->Insert(
            TABLE   => $Table,
            VALUES  => \%row,
            SKIP    => (defined($row{$PrimaryKey}) ? 1 : 0)
          )
        ) || ($Log->Line("Error: Can't insert record. $in{'DB'}->{'ERROR'}", "error") and &$Exit);
      }
        
      $Log->Line("Done.", "success");
    }
    
    $Log->Line("", "downlevel");
  }

  $Log->Line("Removing temporary file... ", "element");
  unlink("$SYSTEM->{'TEMP_PATH'}/import.dat") || ($Log->Line("Error: $!", "error") and &$Exit);
  $Log->Line("Done.", "success");

  $Log->Line("\n\n", "");
  $Log->Line("All done!", "element");
  $Log->Line("", "downlevel");

  &$Exit;
}

1;